package com.xhu.dao.impl;

import com.xhu.bean.pojo.Forecast;
import com.xhu.dao.ForecastDao;
import com.xhu.utils.ConnectionFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ForecastDaoImpl implements ForecastDao {
    @Override
    public int add(Forecast forecast) {
        //定义执行SQL语句
        String  sql="INSERT INTO " +
                "forecast(forecast_id,forecast_title,forecast_month," +
                "dept_name,create_time," +
                "emp_name,forecast_result) " +
                "VALUE(?,?,?,?,?,?,?)";
        //获得连接对象
        Connection connection= ConnectionFactory.openConnection();
        //或者执行对象
        QueryRunner queryRunner=new QueryRunner();
        try {
            return   queryRunner.update(connection,sql,
                    forecast.getForecast_Id(),
                    forecast.getForecast_Title(),
                    forecast.getForecast_Month(),
                    forecast.getDept_Name(),
                    forecast.getCreate_Time(),
                    forecast.getEmp_Name(),
                    forecast.getForecast_Result());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return 0;
    }

    @Override
    public Forecast findById(Integer id) {
        Forecast forecast=null;
        String   sql="select *  from  forecast where forecast_id=?";
        //获得连接对象
        Connection  connection=ConnectionFactory.openConnection();
        //执行对象
        QueryRunner  queryRunner=new QueryRunner();
        try {
            forecast = queryRunner.query(connection, sql, new BeanHandler<>(Forecast.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return forecast;
    }


    @Override
    public int update(Forecast forecast) {
        //定义执行SQL语句
        String  sql="update forecast " +
                " set forecast_title=?,forecast_month=?,dept_name=?," +
                "create_time=?,emp_name=?," +
                "forecast_result=?  where  " +
                "forecast_id=?";
        //获得连接对象
        Connection   connection= ConnectionFactory.openConnection();
        //或者执行对象
        QueryRunner   queryRunner=new QueryRunner();
        try {
            return   queryRunner.update(connection,sql,
                    forecast.getForecast_Title(),
                    forecast.getForecast_Month(),
                    forecast.getDept_Name(),
                    forecast.getCreate_Time(),
                    forecast.getEmp_Name(),
                    forecast.getForecast_Result(),
                    forecast.getForecast_Id());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        String  sql="delete  from  forecast   where  " +
                "forecast_id=?";
        //获得连接对象
        Connection   connection= ConnectionFactory.openConnection();
        //或者执行对象
        QueryRunner   queryRunner=new QueryRunner();
        try {
            return   queryRunner.update(connection,sql,
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return 0;
    }

    @Override
    public int batchDelete(String ids) {
        String  sql="delete  from  forecast   where  " +
                "forecast_id  in ("+ids+")";
        //获得连接对象
        Connection   connection= ConnectionFactory.openConnection();
        //或者执行对象
        QueryRunner   queryRunner=new QueryRunner();
        try {
            return   queryRunner.update(connection,sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return 0;
    }

    @Override
    public List<Forecast> findAll() {
        String  sql="select forecast_id,forecast_title" +
                "   from  forecast order by create_time desc";
        //获得连接对象
        Connection   connection=ConnectionFactory.openConnection();
        //执行对象
        QueryRunner  queryRunner=new QueryRunner();
        //执行获得结果集
        List<Forecast>  list=null;
        try {
            list=queryRunner.
                    query(connection,sql,
                            new BeanListHandler<>(Forecast.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }

        return list;
    }

    @Override
    public Forecast findByMonth(String month) {
        Forecast forecast=null;
        String   sql="select *  from  forecast where forecast_month=?";
        //获得连接对象
        Connection  connection=ConnectionFactory.openConnection();
        //执行对象
        QueryRunner  queryRunner=new QueryRunner();
        try {
            forecast = queryRunner.query(connection, sql, new BeanHandler<>(Forecast.class), month);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }
        return forecast;
    }

    @Override
    public List<Forecast> querPage(Integer start, Integer limit, String name) {
        List<Object>   parms=new ArrayList<>();
        StringBuffer   sql=new StringBuffer("select  * from  forecast ");
        if(name!=null){
            sql.append("  where forecast_month like ?");
            parms.add("%"+name+"%");
        }
        sql.append( " order by create_time desc  limit   ?,?" );
        parms.add(start);
        parms.add(limit);
        //获得连接对象
        Connection   connection=ConnectionFactory.openConnection();
        //执行对象
        QueryRunner  queryRunner=new QueryRunner();
        //执行获得结果集
        List<Forecast>  list=null;
        try {
            list=queryRunner.
                    query(connection,sql.toString(),parms.toArray(),
                            new BeanListHandler<>(Forecast.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionFactory.closeConnection(connection);
        }

        return list;
    }
}
